﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using Emoney.DataAccess;
using LiuYan.Base;
using LiuYan.Entity;


namespace LiuYan.Service
{
    public class OrderService
    {
        /// <summary>
        /// 添加订单
        /// </summary>
        /// <param name="shopcarids"></param>
        /// <returns></returns>
        public int AddOrder(string shopcarids, int userId)
        {
            //try
            //{
            var id = shopcarids.Split(',');
            var sql = "select *," +
"(select Price  from [dbo].[TB_Good_Property] where id=TB_ShopCar.TcId) as tcprice," +
"(select Price  from [dbo].TB_Goods where id=TB_ShopCar.Gid) as gprice," +
"(select DiscountPrice  from [dbo].TB_Goods where id=TB_ShopCar.Gid) as dprice," +
"(select freight from [dbo].TB_Goods where id=TB_ShopCar.Gid) as freight," +
"(select Name from [dbo].[TB_Good_Property] where id=TB_ShopCar.TcId) as tcname," +
"(select Value from [dbo].[TB_Good_Property] where id=TB_ShopCar.TcId) as tcvalue," +
"(select Price from [dbo].[TB_Good_Property] where id=TB_ShopCar.TcId) as tcprice " +
"from [dbo].[TB_ShopCar] where ";
            var temp = "";
            var totalPrice = 0.0;
            var price = 0.0;
            var freight = 0.0;
            var viceordersql = "";
            for (int i = 0; i < id.Length; i++)
            {
                temp += "or id=" + id[i] + " ";
                if (id.Length == 1)
                {
                    temp = " id=" + id[i] + " ";
                }
            }
            temp = id.Length > 1 ? (temp.Substring(2, temp.Length - 2)) : temp;
            sql = sql + temp;
            var shopCarData = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
            foreach (DataRow row in shopCarData.Rows)
            {
                var tppeice = 0.0;//每件商品的价格
                freight += Convert.ToDouble(row["Freight"]);
                var count = Convert.ToInt32(row["Count"].ToString());
                if (row["tcprice"].ToString() != "")//套餐价格不为空
                {
                    totalPrice += Convert.ToDouble(row["tcprice"].ToString()) * count;
                    price = Convert.ToDouble(row["tcprice"].ToString());
                }
                else
                {
                    if (Convert.ToDouble(row["dprice"]).ToString() != "" && Convert.ToDouble(row["dprice"]).ToString() != "0")//折扣价格不为空
                    {
                        totalPrice += Convert.ToDouble(row["dprice"].ToString()) * count;
                        price = Convert.ToDouble(row["dprice"].ToString());
                    }
                    else
                    {
                        // row["tcprice"] = row["tcprice"].ToString().Trim() == "" ? "0" : row["tcprice"];
                        //  double tcp = row["tcprice"].ToString().Trim() == "" ? 0 : Convert.ToDouble(row["tcprice"].ToString());
                        totalPrice += Convert.ToDouble(row["gprice"]) * count;
                        price = Convert.ToDouble(row["gprice"].ToString());
                    }
                }
                viceordersql += " insert into [dbo].[TB_ViceOrder](gid,attr,price,oid,[count])" +
                                " values('" + row["Gid"].ToString() + "','" + (row["tcname"].ToString() +
                                row["tcvalue"].ToString() + " " + Convert.ToDouble(price) + "元") + "','" + price + "',@oid,'" + row["Count"].ToString() + "') ";
            }
            freight = freight / shopCarData.Rows.Count;
            TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
            totalPrice = Convert.ToDouble(totalPrice) + Convert.ToDouble(freight);
            string ordersql = " declare  @oid int " +
                              " insert into [dbo].[TB_Order]([TotalPrice],[OrderNo],[Date],[Status],[Del],[UserId],[Freight])" +
                              "values('" + Convert.ToDecimal(totalPrice) + "','" + "sp" + Convert.ToInt64(ts.TotalSeconds) + "','" + DateTime.Now + "','1','0','" + userId + "','" + freight + "') " +
                              " set @oid=@@IDENTITY ";
            sql = "begin transaction " + ordersql + viceordersql +
                  "delete [dbo].[TB_ShopCar] where " + temp +
                " commit transaction ";

            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
            //}
            //catch (Exception e)
            //{
            //    return 0;
            //}
        }

        /// <summary>
        /// 前台添加订单
        /// </summary>
        /// <param name="money"></param>
        /// <param name="userid"></param>
        /// <param name="spids"></param>
        /// <param name="count"></param>
        /// <param name="prices"></param>
        /// <param name="discount"></param>
        /// <param name="orderno"></param>
        /// <returns></returns>
        public int AddOrder(double money, int userid, string spids, string count, string prices,
            string discount, string orderno, double sjmoney, double hyye)
        {
            string ordersql = " declare  @oid int " +
                                 " insert into [dbo].[TB_Order]([TotalPrice],[OrderNo],[Date],[Status],[Del],[UserId],[Freight],Sort,Discount,Sjmoney,Dk)" +
                                 "values('" + Convert.ToDecimal(money) + "','" + "sp" + orderno + "','" + DateTime.Now + "','4','0','" +
                                 userid + "','" + 0 + "','1','" + discount + "','" + sjmoney + "','" + (hyye > 0 ? "1" : "0") + "') " +
                                 " set @oid=@@IDENTITY ";
            var ids = spids.Split(',');
            var counts = count.Split(',');
            var price = prices.Split(',');
            string viceordersql = "";
            string jiancountsql = "";

            for (int i = 0; i < ids.Length; i++)
            {
                viceordersql += " insert into [dbo].[TB_ViceOrder](gid,attr,price,oid,[count])" +
                                  " values('" + ids[i] + "','','" + price[i] + "',@oid,'" + counts[i] + "')";

                jiancountsql += "update TB_Goods set Count=Count-" + counts[i] + " where id=" + ids[i];
            }
            string sqldk = "";
            if (hyye > 0)
            {
                sqldk = " insert into dbo.TB_UserMoney(UserId,money)values('" + userid + "','" + Convert.ToDouble("-" + money) + "') ";
            }

            string sql = "begin transaction " + ordersql + viceordersql + sqldk + jiancountsql + " commit transaction ";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);

        }

        /// <summary>
        /// 订单状态数量
        /// </summary>
        /// <param name="status"></param>
        /// <returns></returns>
        public int OrderStatusCount(int status, int userid)
        {
            string sql = "select count(*) as count from [dbo].[TB_Order] where del=0  and userid= '" + userid + "' and status=" + status;
            return Convert.ToInt32(SqlHelper.ExecuteScalar(Connection.conStr, CommandType.Text, sql));
        }

        /// <summary>
        /// 获取订单
        /// </summary>
        /// <param name="sid"></param>
        /// <param name="pageCount"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public DataSet GetOrder(int sid, int pageCount, int pageIndex, int userId)
        {
            string sql = " select count(*) as count from [dbo].[TB_Order] where del=0 and  Status=" + sid + " and UserId=" + userId + "  " +
                         "select top(" + pageIndex + ")*," +
                         "(select ExpressName from TB_Express where TB_Express.id= TB_Order.wlid ) as ExpressName " +
                         " from [dbo].[TB_Order] where id not in (select top(" + pageCount + ")Id from" +
                         " [dbo].[TB_Order] order by id desc ) and del=0 and status=" + sid + " and UserId=" + userId + " order by id desc";
            var set = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql);

            DataTable table = new DataTable();
            table.Columns.Add("Id", typeof(int));
            table.Columns.Add("GoodsName", typeof(string));
            table.Columns.Add("Img1", typeof(string));
            table.Columns.Add("Attr", typeof(string));
            table.Columns.Add("Price", typeof(string));
            table.Columns.Add("Count", typeof(string));
            table.Columns.Add("OId", typeof(int));
            table.Columns.Add("GId", typeof(int));

            foreach (DataRow row in set.Tables[1].Rows)
            {

                sql = " select a.Id,a.Oid,a.Attr,a.Price,a.Count,b.Name,b.Img1,a.GId from [dbo].[TB_ViceOrder] as a,[dbo].[TB_Goods] as b where a.Oid=" + row["Id"] + " and b.Id=a.GId";
                var temp = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
                foreach (DataRow temprow in temp.Rows)
                {
                    DataRow rows = table.NewRow();
                    rows["Id"] = temprow["Id"];
                    rows["GoodsName"] = temprow["Name"];
                    rows["Img1"] = temprow["Img1"];
                    rows["Attr"] = temprow["Attr"];
                    rows["Price"] = temprow["Price"];
                    rows["Count"] = temprow["Count"];
                    rows["OId"] = temprow["Oid"];
                    rows["GId"] = temprow["GId"];

                    table.Rows.Add(rows);
                }
            }
            set.Tables.Add(table);
            set.Tables[0].TableName = "count";
            set.Tables[1].TableName = "data";
            set.Tables[2].TableName = "data1";
            return set;
        }
        /// <summary>
        /// 后台获取订单
        /// </summary>
        /// <param name="sid"></param>
        /// <param name="pageCount"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public DataSet GetOrder1(int sid, int pageCount, int pageIndex, string orderNo)
        {
            SqlParameter parameter = new SqlParameter("@orderNo", SqlDbType.VarChar, 70);
            parameter.Value = orderNo;
            var where = "";
            where = orderNo == "" ? " " : "and orderNo=@orderNo ";
            where += sid == 0 ? where : ("and [status]=" + sid);
            string sql = " select count(*) as count from [dbo].[TB_Order] where del=0  " +
                        where +
                               "   select top(" + pageIndex + ")*," +
                         "(select [NcName] from [dbo].[TB_User] where Id=TB_Order.UserId) as UserName," +
                          "(select ExpressName from TB_Express where TB_Express.id= TB_Order.wlid ) as ExpressName " +
                         " from [dbo].[TB_Order] where id not in (select top(" +
                               pageCount + ")Id from [dbo].[TB_Order] order by id desc )" + where +
                               " and del=0  order by id desc";
            var set = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql, parameter);

            DataTable table = new DataTable();
            table.Columns.Add("Id", typeof(int));
            table.Columns.Add("GoodsName", typeof(string));
            table.Columns.Add("Img1", typeof(string));
            table.Columns.Add("Attr", typeof(string));
            table.Columns.Add("Price", typeof(string));
            table.Columns.Add("Count", typeof(string));
            table.Columns.Add("OId", typeof(int));
            foreach (DataRow row in set.Tables[1].Rows)
            {

                sql = " select a.Id,a.Oid,a.Attr,a.Price,a.Count,b.Name,b.Img1,a.GId from [dbo].[TB_ViceOrder] as a,[dbo].[TB_Goods] as b where a.Oid=" + row["Id"] + " and b.Id=a.GId";
                var temp = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
                foreach (DataRow temprow in temp.Rows)
                {
                    DataRow rows = table.NewRow();
                    rows["Id"] = temprow["Id"];
                    rows["GoodsName"] = temprow["Name"];
                    rows["Img1"] = temprow["Img1"];
                    rows["Attr"] = temprow["Attr"];
                    rows["Price"] = temprow["Price"];
                    rows["Count"] = temprow["Count"];
                    rows["OId"] = temprow["Oid"];
                    table.Rows.Add(rows);
                }
            }
            set.Tables.Add(table);
            set.Tables[0].TableName = "count";
            set.Tables[1].TableName = "data";
            set.Tables[2].TableName = "data1";
            return set;
        }

        /// <summary>
        /// 删除订单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelOrder(int id)
        {
            string sql = " begin transaction " +
                         "delete [dbo].[TB_ViceOrder] where oid=" + id + " delete [dbo].[TB_Order] where id=" + id +
                         " commit transaction";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
        }
        /// <summary>
        /// 删除订单
        /// </summary>
        /// <param name="orderno">订单号，多条删除</param>
        /// <returns></returns>
        public int DelOrder1(string orderno)
        {
            var ids = orderno.Split(',');
            var where = "";
            for (var i = 0; i < ids.Length; i++)
            {
                where += "or OrderNo='" + ids[i] + "'";
            }
            where = where.Substring(2, where.Length - 2);
            string sql = " beg" +
                         "in transaction " +
                          " update  [dbo].[TB_Order] set del=1 where " + where +
                         " commit transaction";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
        }
        /// <summary>
        /// 获取订单的id或者编号
        /// </summary>
        /// <param name="str"></param>
        /// <param name="att"> 参数：“id”  “no” </param>
        /// <returns></returns>
        public string GetOrderIdOrNo(string str, string att)
        {
            SqlParameter parameter = new SqlParameter("@str", SqlDbType.VarChar, 70);
            parameter.Value = str;
            SqlParameter parameter1 = new SqlParameter("@str", SqlDbType.Int);
            parameter1.Value = str;
            string sql = "select Id,OrderNo from [TB_Order] where ";
            sql = att == "id" ? (sql + " orderno=@str ") :
            (sql + " id=@str ");
            var table = att == "no" ?
                SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql, parameter1).Tables[0]
                : SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql, parameter).Tables[0]
                ;
            if (att == "id")
            {
                return table.Rows[0]["Id"].ToString();
            }
            return table.Rows[0]["OrderNo"].ToString();
        }

        /// <summary>
        /// 获取订单数量
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int GetOrderCount(int userId)
        {
            string sql = "select count(*) as count from  [dbo].TB_Order where userid=" + userId + " and del=0";
            return Convert.ToInt32(SqlHelper.ExecuteScalar(Connection.conStr, CommandType.Text, sql));
        }

        /// <summary>
        /// 修改订单状态
        /// 
        /// </summary>
        /// <param name="orderid"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public int EditOrderStatus(string orderid, int value)
        {
            string sql = "update TB_Order set status='" + value + "' where orderno='" + orderid + "'";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
            ;
        }

        /// <summary>
        /// 发货
        /// </summary>
        /// <param name="orderno"></param>
        /// <param name="danhao"></param>
        /// <param name="wlName"></param>
        /// <returns></returns>
        public int FaHuo(string orderno, string danhao, int wlName)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@orderno",SqlDbType.VarChar,50),
   new SqlParameter("@danhao",SqlDbType.VarChar,50),
     new SqlParameter("@wlName",SqlDbType.Int),
            };
            parameters[0].Value = orderno;
            parameters[1].Value = danhao;
            parameters[2].Value = wlName;
            string sql = "update [dbo].[TB_Order] set [WlId]=@wlName,[WlNo]=@danhao,status=3 where [OrderNo]=@orderno";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql, parameters);
        }

        /// <summary>
        /// 收货
        /// </summary>
        /// <param name="orderno"></param>
        /// <returns></returns>
        public int ShouHuo(string orderno)
        {
            SqlParameter parameter = new SqlParameter("@orderno", SqlDbType.VarChar, 50);
            parameter.Value = orderno;
            string sql = "update TB_Order set status=4 where OrderNo=@orderno";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql, parameter);
        }

        /// <summary>
        /// 添加新品和热销
        /// </summary>
        /// <param name="gid"></param>
        /// <param name="tb"></param>
        /// <returns></returns>
        public int EditXpGood(int gid, string tb)
        {
            string sql = "insert into dbo." + tb + "(Gid,Del)values(" + gid + ",0)";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
        }



        /// <summary>
        /// 获取一条订单信息
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public DataSet GetOneOrder(string orderNo)
        {
            string sql = "select * from dbo.TB_Order where OrderNo='" + orderNo + "'";
            sql += " select a.Id,a.Name,a.Img1,b.Id,b.Attr,b.Price,b.Count from TB_Goods as a,TB_ViceOrder as b,TB_Order as c where a.Id=b.gid and b.oid=c.id and c.orderno='" + orderNo + "'";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql);
        }

        /// <summary>
        /// 编辑订单地址
        /// </summary>
        /// <param name="addid"></param>
        /// <param name="orderno"></param>
        /// <returns></returns>
        public int EditAdd(int addid, string orderno)
        {
            string sql = "select Address,Tell from TB_Address where id=" + addid;
            var table = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
            sql = "update TB_Order set Address='" + table.Rows[0]["Address"] + "',Tell='" + table.Rows[0]["Tell"] + "' where OrderNo='" + orderno + "'";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
        }


    }
}
